<!DOCTYPE html>
<html lang="zh-CN"><head>
	<title>sql执行接口 - 数据仓库  open api</title>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="shortcut icon" href="./favicon.ico">
	<script type="text/javascript" src="js/plugin/jquery.min.js"></script>
	<script type="text/javascript" src="js/plugin/fullscreen.js"></script>
	<script type="text/javascript" src="js/apiTest.js"></script>
	<script type="text/javascript" src="js/plugin/bootstrap-3.3.5/js/bootstrap.min.js"></script>
	<script type="text/javascript" src="js/plugin/bootstrap-3.3.5/js/bootstrap-select.min.js"></script>
	<script type="text/javascript" src="js/plugin/bootstrap-3.3.5/js/defaults-zh_CN.min.js"></script>
    <script type="text/javascript" src="js/plugin/jsonview-v1.2.0/dist/jquery.jsonview.js"></script>
	<script type="text/javascript" src="js/plugin/page.js"></script>
	<script type="text/javascript" src="js/sybn_bootstrap.js"></script>
	<script type="text/javascript" src="js/sybn_common.js"></script>
    <script type="text/javascript" src="js/sybn_desc_table.js"></script>
	<script type="text/javascript" src="js/plugin/sql/codemirror.js"></script>
	<script type="text/javascript" src="js/plugin/sql/matchbrackets.js"></script>
	<script type="text/javascript" src="js/plugin/sql/sql.js"></script>
	<script type="text/javascript" src="js/plugin/sql/show-hint.js"></script>
	<script type="text/javascript" src="js/plugin/sql/sql-hint3.js"></script>
	<script type="text/javascript" src="js/plugin/sql/sql-hint-sybn.js"></script>
	<script type="text/javascript" src="js/sybn_demo_sql.js"></script>
	<link rel="stylesheet" href="css/fontawesome-4.2.0_ie7/4.2.0/css/font-awesome.min.css">
	<link rel="stylesheet" href="js/plugin/bootstrap-3.3.5/css/bootstrap.min.css">
	<link rel="stylesheet" href="js/plugin/bootstrap-3.3.5/css/bootstrap-select.min.css">
    <link rel="stylesheet" href="js/plugin/jsonview-v1.2.0/dist/jquery.jsonview.css" />
	<link rel="stylesheet" href="css/bootstrap_sybn_custom.css">
	<link rel="stylesheet" href="css/api_test.css">
	<link rel="stylesheet" href="css/plugin/sql/codemirror.css" />
	<link rel="stylesheet" href="css/plugin/sql/show-hint.css" />
	<style>
		textarea,.form-control.CodeMirror{height: calc(100% - 35px);width:100%;}
		.panel{height:100%}
		.tab-content{height: calc(100% - 32px)}
		.panel-body{height: calc(100% - 20px)}
	</style>
</head>
<body>
<ul class="nav nav-tabs">
  <li><strong class="title">sql 转 aggregate</strong></li>
  <li class="active"><a href="#p_conver" data-toggle="tab" aria-expanded="false">转换</a></li>
  <li class=""><a href="#p_info" data-toggle="tab" aria-expanded="false">帮助</a></li>
  <li class="floatRight padding3">
   <!--
    <button type="button" class="btn btn-danger btn-xs" onclick="$('#cacheRefresh').click()">刷新缓存</button>-->
  </li>
</ul>
<div class="tab-content">

  <div id="p_conver" class="panel panel-primary tab-pane active">
    <div class="panel-heading text-primary">
      <span class="p_title">查询转换</span>
    </div>

    <div class="panel-body sybn-api-demo collapse in">

      <div class="col-xs-12 padding0">
	      <span>请在左侧输入sql然后</span>
	      <span class="btn btn-success converSql submit btn-xs">点此转换</span> 或
          <span class="btn btn-success converSql submit btn-xs pretty">点此转换并格式化</span>
	      <span>更多信息请查看帮助 (比如:如何查询时间,如何unwind,如何join)</span>
      </div>

      <div class="col-xs-6 padding0">

        <textarea id="sql_textarea" name="sql" class="sql_textarea" style="margin-top:5px;display: block!important;">
-- 请在此写入想要转换的sql语句(mongo不支持join，以下是利用lookup实现的三表联查demo)

# 从 ticket 表统计用户注册地址城市，并显示 city 表中的城名称
select
  city_id, count(*) as count_num,
  (select city_name from city where a.city_id = city.id) as name
from (

  # 获取订单及订单用户的城市id
  select
    user_id,
    (select city_id from user where ticket.user_id= user.id) as city_id # 从用户表找城市id
  from ticket
  where ticket_time >= str_to_date('2019-10-01', '%Y-%m-%d')
    and ticket_time < str_to_date('2019-10-08', '%Y-%m-%d') # Mongo 查时间必须转Date格式

) a group by city_id
        </textarea>
      </div>

      <div class="col-xs-6 padding0">
        <textarea id="aggregate_textarea" name="aggregate" class="sql_textarea" style="margin-top:5px;display: block!important;">#请点击转换按钮在此查看结果</textarea>
      </div>
    </div>
  </div>


  <div id="p_info" class="panel panel-primary tab-pane">
     <div class="panel-heading text-primary">
       <span class="p_title">关于</span>
     </div>
    <div class="panel-body sybn-api-demo collapse in">


      <div class="panel panel-warning w100">
        <div class="panel-heading text-primary">
          <span class="p_title">帮助信息</span>
        </div>
        <div class="panel-body sybn-api-demo collapse in">
          <p style="font-size:14px;">
            <strong>文档地址</strong><br>
            <span class="l_content">
                <a href="/2019/04/07/sql_query-convert-mongo_aggregate/" target="_blank">/2019/04/07/sql_query-convert-mongo_aggregate/</a>
            </span><br>
          </p>
          <p style="font-size:14px;">
            <strong>如何查询时间</strong><br>
            <span class="l_content">mongo/solr的查询操作是区分数据类型的,如果需要查询时间可以使用如下方法:</span><br>
            <span class="l_content">SQL: <span class="text-primary">where datetime > "2019-01-01"</span></span><br>
            <span class="l_content">mongo/solr: <span class="text-primary">where datetime > STR_TO_DATE('2019-01-21', '%Y-%m-%d')</span></span><br>
            <span class="l_content">解析引擎会在生成mongo/solr查询前,将<span class="text-primary">STR_TO_DATE('2019-01-21', '%Y-%m-%d')</span>替换为对应的时间</span><br>
            <span class="l_content">解析引擎可以识别大部分sql规范中的UDF函数, 比如: <span class="text-primary">where datetime > DATE_ADD(now(),INTERVAL -1 DAY)</span> 等</span><br>
          </p>
          <p style="font-size:14px;">
            <strong>如何unwind</strong><br>
            <span class="l_content">unwind是指将某一个数组列拆分为多行,具体在请百度搜索:<span class="text-primary">$unwind</span></span><br>
            <span class="l_content">解析引擎可以转换mongo的unwind, 执行引擎可以对List/Stream执行unwind:</span><br>
            <span class="l_content">语法: <span class="text-primary">select a,count(*) as b from table group by unwind(a_list) as a</span></span><br>
            <span class="l_content">支持多字段 unwind</span>
          </p>
          <p style="font-size:14px;">
            <strong>如何join</strong><br>
            <span class="l_content">执行引擎可以对List/Stream/mysql/mongo/solr/hbase等数据源,执行互相join:</span><br>
            <span class="l_content">当前页面仅仅是解析引擎的demo,无法体验执行引擎的功能</span><br>
            <span class="l_content">文档地址: <a href="/2018/12/20/sybn-dao-multiple-impl/" target="_blank">/2018/12/20/sybn-dao-multiple-impl/</a></span>
          </p>
        </div>
      </div>

      <div class="panel panel-success w100">
        <div class="panel-heading text-primary">
          <span class="p_title">近期更新:</span>
        </div>
        <div class="panel-body collapse in">
          <p>
            <strong>2019-07</strong><br>
            <span>第一版页面</span><br>
          </p>
        </div>
      </div>
    </div>
  </div>

</div>
</body>
<script>
window.onload = function() {

	var sql = getQueryString("sql");

	var sql_demo = getQueryString("sql_demo");
	if (sql_demo && getSybnDemoSql) {
		sql = getSybnDemoSql()[sql_demo];
	}
	if (sql) {
		var sql_editor = $('[name=sql]');
		sql_editor.val(sql);
	}

  // CodeMirror 有 bug 暂时不开启
  // initSqlEditor({});

  $('.CodeMirror').addClass("form-control");

  $('body').on('click', '.converSql', function(e) {
	var sql_editor = window[$('[name=sql]').data("editor")]
	var sql = sql_editor ? sql_editor.getValue() : $('[name=sql]').val();
	var pretty = $(this).hasClass("pretty");
	// 防止双击触发单击
	$.ajax({
	     url: "api/sql/aggregate.json",
	     type:'post',
	     dataType:'json',
	     async:true,//异步请求
	     cache:false,
	     data: {sql:sql,pretty:pretty},
	     //执行成功的回调函数
	     success:function(data) {
	      	  if (data.status && data.status == 500){
	      	    alert(data.message ? "操作失败: " + data.message : "操作失败!");
	      	  } else if (!data.result){
	      	    alert(data.msg ? "操作失败: " + data.msg : "操作失败!");
	      	  } else {
                 $('[name=aggregate]').val(data.aggregate);
                 var aggregate_editor = window[$('[name=aggregate]').data("editor")];
                 if (aggregate_editor) {
                     aggregate_editor.setValue(data.aggregate);
                 }
	         }
	     },
	     //执行失败或错误的回调函数
	     error:function(data) {
	         alert("操作失败");
	     }
	 });
  });
};
</script>
</html>
